#!/bin/bash

#Created by turk 2016-10-26
#Version 1.0 
#2016-12-06 modified by turk remove ip format function



if [ $# -lt 1 ]; then
	echo `date +"%Y-%m-%d %H:%M:%S"`  "Usage:<DATE1 YYYYMMDD> <DATA2 YYYY-MM-DD>"
	exit 1
fi

DATE1=$1
DATE2=$2

WORKPATH=/home/hadoop/work/
JAVALIB=${WORKPATH}/lib/
LOGFILE=${WORKPATH}/log/vio_app_stat.log
LOGPATH=${WORKPATH}/log/

#--ORACLE--
IP=192.168.5.25
USERNAME=vio
PASSWORD=h16aug8v3w


echo `date +"%Y-%m-%d %H:%M:%S"`  "-----------------------------" | tee -a $LOGFILE
echo `date +"%Y-%m-%d %H:%M:%S"`  "Shell Version 1.0 (2016-10-26)"     | tee -a $LOGFILE
echo `date +"%Y-%m-%d %H:%M:%S"`  "UBAS-VIO APP STAT"  | tee -a $LOGFILE
echo `date +"%Y-%m-%d %H:%M:%S"`  "-----------------------------"  | tee -a $LOGFILE
echo `date +"%Y-%m-%d %H:%M:%S"`  "Start..."  | tee -a $LOGFILE



udfstr="add jar hdfs://nameservice1/hive/udf/ipconvert.jar;\
CREATE TEMPORARY FUNCTION IpToString AS 'com.aotain.common.IpToString';\
CREATE TEMPORARY FUNCTION IpToLong AS 'com.aotain.common.IpToLong';"

#--------------vio_app_stat_ip_d----------------------
sql="insert overwrite directory '/tmp/tmp_vio_app_stat_ip_d' \
 select '$DATE2' reporttime,b.taskid,c.ipsegid,IpToString(a.destinationip) ip,a.destinationport,d.appid,d.apptype,d.appname,d.apptypename,\
 null domain,case when protocolid=0 or protocolid=1 then '' when protocolid=2 then 'RSTP' when protocolid=3 then 'VoIP' when protocolid=4 then 'FTP' \
 when protocolid=5 then 'SMTP' when protocolid=6 then 'POP3' when protocolid=7 then 'IMAP' when protocolid=8  then 'DNS' when protocolid=9 then 'P2P' \
 when protocolid=10 then 'GAME' when protocolid=11 then 'IM' else 'Other' end PROTOCAL, \
 sum(upstreamoctets) upstreamoctets,sum(upstreampacket) upstreampacket,sum(dnstreamoctets) dnstreamoctets,sum(dnstreampacket) dnstreampacket \
 from (select dt,destinationip,destinationport,packettype,packetsubtype,protocolid,upstreamoctets,upstreampacket,dnstreamoctets,dnstreampacket from ods_vio_nohttp union all \
select dt,destinationip,destinationport,packettype,packetsubtype,protocolid,upstreamoctets,upstreampacket,dnstreamoctets,dnstreampacket from ods_vio_http) a join vio.vio_flowmirror_task b join vio.vio_flowmirror_ipseg c \
 on c.taskid = b.taskid \
 join vio.vio_cfg_apptype d on d.apptype = a.packettype and d.appid = a.packetsubtype \
 where dt = $DATE1 and a.destinationip >= c.startip and a.destinationip <= c.endip and d.appid>0 and d.apptype>0 \
 group by b.taskid,c.ipsegid,IpToString(a.destinationip),a.destinationport,d.appid,d.apptype,d.appname,d.apptypename,a.protocolid;"
hive -e "$udfstr$sql" > $LOGPATH/vio_app_stat_ip_d.log 2>&1

#--------------vio_app_stat_ipseg_d----------------------
sql="insert overwrite directory '/tmp/tmp_vio_app_stat_ipseg_d' \
 select '$DATE2' reporttime,c.ipsegid,c.ipsegment,b.taskid,d.appid,d.apptype,d.appname,d.apptypename,\
 sum(upstreamoctets) upstreamoctets,sum(upstreampacket) upstreampacket,sum(dnstreamoctets) dnstreamoctets,sum(dnstreampacket) dnstreampacket\
 from (select dt,destinationip,destinationport,packettype,packetsubtype,protocolid,upstreamoctets,upstreampacket,dnstreamoctets,dnstreampacket from ods_vio_nohttp union all \
select dt,destinationip,destinationport,packettype,packetsubtype,protocolid,upstreamoctets,upstreampacket,dnstreamoctets,dnstreampacket from ods_vio_http) a join vio.vio_flowmirror_task b join vio.vio_flowmirror_ipseg c on c.taskid = b.taskid \
 join vio.vio_cfg_apptype d on d.apptype = a.packettype and d.appid = a.packetsubtype\
 where dt = $DATE1 and a.destinationip >= c.startip and a.destinationip <= c.endip and d.appid>0 and d.apptype>0 \
 group by c.ipsegid,c.ipsegment,b.taskid,d.appid,d.apptype,d.appname,d.apptypename;"
hive -e "$udfstr$sql" > $LOGPATH/vio_app_stat_ipseg_d.log 2>&1

#---------------vio_app_stat_task_d----------------------
sql="insert overwrite directory '/tmp/tmp_vio_app_stat_task_d' \
 select '$DATE2' reporttime,b.taskid,b.taskname,d.appid,d.apptype,d.appname,d.apptypename, \
 sum(upstreamoctets) upstreamoctets,sum(upstreampacket) upstreampacket,sum(dnstreamoctets) dnstreamoctets,sum(dnstreampacket) dnstreampacket \
 from (select dt,destinationip,destinationport,packettype,packetsubtype,protocolid,upstreamoctets,upstreampacket,dnstreamoctets,dnstreampacket from ods_vio_nohttp union all \
select dt,destinationip,destinationport,packettype,packetsubtype,protocolid,upstreamoctets,upstreampacket,dnstreamoctets,dnstreampacket from ods_vio_http) a join vio.vio_flowmirror_task b join vio.vio_flowmirror_ipseg c on c.taskid = b.taskid \
 join vio.vio_cfg_apptype d on d.apptype = a.packettype and d.appid = a.packetsubtype \
 where dt = $DATE1 and a.destinationip >= c.startip and a.destinationip <= c.endip and d.appid>0 and d.apptype>0 \
 group by b.taskid,b.taskname,d.appid,d.apptype,d.appname,d.apptypename;"
hive -e "$udfstr$sql" > $LOGPATH/vio_app_stat_task_d.log 2>&1

#-----------------SQOOP----------------------------
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$JAVALIB/hades-project-0.0.1-SNAPSHOT.jar

sqoop export --connect jdbc:oracle:thin:@$IP:1521:BSMP \
--username $USERNAME --password=$PASSWORD \
--table VIO_APP_STAT_IP_D  \
--columns REPORTTIME,TASKID,IPSEGID,IP,PORT,APPID,APPTYPE,APPNAME,APPTYPENAME,DOMAIN,PROTOCAL,UPSTREAMOCTETS,UPSTREAMPACKET,DNSTREAMOCTETS,DNSTREAMPACKET \
--export-dir /tmp/tmp_vio_app_stat_ip_d \
--fields-terminated-by '\001' \
-m 2 > $LOGPATH/SQOOP_tmp_vio_app_stat_ip_d.log 2>&1

sqoop export --connect jdbc:oracle:thin:@$IP:1521:BSMP \
--username $USERNAME --password=$PASSWORD \
--table VIO_APP_STAT_IPSEG_D  \
--columns REPORTTIME,IPSEGID,IPSEGMENT,TASKID,APPID,APPTYPE,APPNAME,APPTYPENAME,UPSTREAMOCTETS,UPSTREAMPACKET,DNSTREAMOCTETS,DNSTREAMPACKET \
--export-dir /tmp/tmp_vio_app_stat_ipseg_d \
--fields-terminated-by '\001' \
-m 2 > $LOGPATH/SQOOP_tmp_vio_app_stat_ipseg_d.log 2>&1

sqoop export --connect jdbc:oracle:thin:@$IP:1521:BSMP \
--username $USERNAME --password=$PASSWORD \
--table VIO_APP_STAT_TASK_D  \
--columns REPORTTIME,TASKID,TASKNAME,APPID,APPTYPE,APPNAME,APPTYPENAME,UPSTREAMOCTETS,UPSTREAMPACKET,DNSTREAMOCTETS,DNSTREAMPACKET \
--export-dir /tmp/tmp_vio_app_stat_task_d \
--fields-terminated-by '\001' \
-m 2 > $LOGPATH/tmp_vio_app_stat_task_d.log 2>&1


echo `date +"%Y-%m-%d %H:%M:%S"`      "Quit" | tee -a $LOGFILE

exit 0
